﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using System.Text;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class InventoryMgtDA : IInventoryMgtDA
    {
        private string LOCATION_ID = "@Loc";
        private string LOTATTRIBUTE_ID = "@LotAttributeID";
        private string LPN = "@LPN";
        private string OWNER_ID = "@OwnerID";
        private string QTY = "@QTY";
        private string SKU = "@Sku_ID";
        private string SQL_GET_INVENTORY_INFO = "SELECT  1 AS ROWID, ILLL.WH_ID AS WH_ID, ILLL.LOT AS LOT, ILLL.LOC AS LOC, ILLL.LPN_ID AS LPN_ID, ILLL.OWNER_ID AS OWNER_ID, ILLL.SKU_ID AS SKU_ID, ILLL.QTY AS QTY, ILLL.QTY - ILLL.ALLOCATED_QTY - ILLL.PICKED_QTY - ILLL.EXPECTED_QTY - ILLL.PICKINPROCESS_QTY - ILLL.PENDING_MOVE_IN AS AVAILABLE_QTY, ILLL.ALLOCATED_QTY AS ALLOCATED_QTY, ILLL.PICKED_QTY AS PICKED_QTY, ILLL.EXPECTED_QTY AS EXPECTED_QTY, ILLL.PICKINPROCESS_QTY AS PICKINPROCESS_QTY, ILLL.PENDING_MOVE_IN AS PENDING_MOVE_IN, ILLL.ARCHIVE_QTY AS ARCHIVE_QTY, ILLL.ARCHIVE_DATE AS ARCHIVE_DATE, ILLL.STATUS AS STATUS, ILLL.TS AS TS, ILLL.CREATED_BY AS CREATED_BY, ILLL.CREATED_DATE AS CREATED_DATE, ILLL.UPDATED_BY AS UPDATED_BY, ILLL.UPDATED_DATE  AS UPDATED_DATE , SKU.DESCR AS SKU_DESCR,LA.LOT_ATTR01,LA.LOT_ATTR02,LA.LOT_ATTR03,LA.LOT_ATTR04,LA.LOT_ATTR05,LA.LOT_ATTR06,LA.LOT_ATTR07,LA.LOT_ATTR08,LA.LOT_ATTR09,LA.LOT_ATTR10,LA.PRODUCE_DATE,LA.EXPIRY_DATE,LA.RECEIPT_DATE,LA.EXTERNAL_LOT,LA.VENDOR_ID FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOT_ATTRIBUTE LA ON ILLL.LOT = LA.LOT LEFT JOIN @_@SKU SKU ON ILLL.OWNER_ID = SKU.OWNER_ID AND ILLL.SKU_ID = SKU.SKU_ID";
        private string sqlStrByLocationID = " SELECT DISTINCT LOC,VI.OWNER_ID,SKU_ID,DESCR,DESCR2,SUM(QTY) AS QTY, QTY * CUBE AS CUBEONHAND, CUBIC_CAPACITY- QTY * CUBE AS CUBE_AVAILABLE, CAST((CASE WHEN CUBIC_CAPACITY <> 0 THEN QTY * CUBE / CUBIC_CAPACITY * 100 ELSE 0 END) AS varchar) + '%' AS CUBE_ONHAND_PERCENTAGE, STATUS,LOCATION_ID,SKU_CLASS_ID,SKU_CLASS_DESCR FROM @_@vINV_LOC VI INNER JOIN SYSADM.USER_OWNER UO ON VI.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID @CONDITION@ GROUP BY LOC ,VI.OWNER_ID,SKU_ID,CUBIC_CAPACITY,QTY,[CUBE],DESCR,[STATUS],LOCATION_ID,DESCR2,SKU_CLASS_ID,SKU_CLASS_DESCR ";
        private string sqlStrByLotID = " SELECT DISTINCT LOT,VI.OWNER_ID,VI.SKU_ID,VI.DESCR,VI.DESCR2,QTY,AVAILABLE,ALLOCATED_QTY,PREALLOCATED_QTY,ONHOLD_QTY,PICKED_QTY,STATUS,LAC.DESCR AS SKU_PROPERTY,SKU_CLASS_ID,SKU_CLASS_DESCR,VENDOR_ID  FROM @_@vINV_LOT VI   INNER JOIN SYSADM.USER_OWNER UO ON VI.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON VI.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性' @CONDITION@";
        private string sqlStrByLotIDAndLoactionID = "SELECT DISTINCT VI.LOT,VI.LOC,VI.OWNER_ID,VI.SKU_ID,VI.DESCR,VI.DESCR2,SUM(QTY) AS QTY,LAC.DESCR AS SKU_PROPERTY,SKU_CLASS_ID,SKU_CLASS_DESCR,VENDOR_ID  FROM @_@vINV_LOT_LOC VI  INNER JOIN SYSADM.USER_OWNER UO ON VI.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON VI.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性' @CONDITION@ GROUP BY VI.LOT,VI.LOC,VI.OWNER_ID,VI.SKU_ID,VI.DESCR,VI.DESCR2,LAC.DESCR,VI.SKU_CLASS_ID,SKU_CLASS_DESCR,VENDOR_ID";
        private string sqlStrByLotIDAndLoactionIDAndLpn = " SELECT DISTINCT VI.OWNER_ID,VI.SKU_ID,VI.DESCR,VI.DESCR2,VI.LOT,VI.LPN_ID,VI.LOC,QTY,AVAILABLE,STATUS,ALLOCATED_QTY,PICKED_QTY,PICKINPROCESS_QTY,PENDING_MOVE_IN,LAC.DESCR  AS SKU_PROPERTY,SKU_CLASS_ID,SKU_CLASS_DESCR,SKU_CLASS_DESCR2,VENDOR_ID  FROM @_@vINV_LOT_LOC_LPN VI  INNER JOIN SYSADM.USER_OWNER UO ON VI.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON VI.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性' @CONDITION@";
        private string sqlStrByLotIDAndLpn = "SELECT DISTINCT VI.LOT,LPN_ID,SUM(QTY) AS QTY,VI.OWNER_ID,VI.SKU_ID,VI.DESCR,VI.DESCR2,SKU_CLASS_ID,SKU_CLASS_DESCR,LAC.DESCR AS SKU_PROPERTY,VENDOR_ID  FROM @_@vINV_LOT_LPN VI INNER JOIN SYSADM.USER_OWNER UO ON VI.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON VI.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性' @CONDITION@ GROUP BY VI.LOT,VI.LPN_ID,VI.OWNER_ID,VI.SKU_ID,VI.DESCR,LAC.DESCR,VI.DESCR2,SKU_CLASS_ID,SKU_CLASS_DESCR,VENDOR_ID";
        private string sqlStrByLpn = "SELECT DISTINCT LPN_ID,QTY,STATUS,PACK_ID,PUTAWAY_TI,PUTAWAY_HI FROM @_@vINV_LPN VI @CONDITION@";
        private string sqlStrByOwnerID = " SELECT DISTINCT VI.OWNER_ID, SUM(QTY) AS QTY, SUM(PICKED_QTY) AS PICKED_QTY, SUM(ALLOCATED_QTY) AS ALLOCATED_QTY  FROM @_@vINV_OWNER VI INNER JOIN SYSADM.USER_OWNER UO ON VI.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID @CONDITION@ GROUP BY VI.OWNER_ID ";
        private string sqlStrBySku = "SELECT DISTINCT VI.OWNER_ID,SKU_ID,DESCR,DESCR2,SUM(QTY) AS QTY,SUM(ONHOLD_QTY) AS ONHOLD_QTY,SUM(ALLOCATED_QTY) AS ALLOCATED_QTY,SUM(PICKED_QTY) AS PICKED_QTY, SUM(EXPECTED_QTY) AS EXPECTED_QTY,SUM(PICKINPROCESS_QTY) AS PICKINPROCESS_QTY,SUM(PENDING_MOVE_IN) AS PENDING_MOVE_IN, SUM(PREALLOCATED_QTY) AS PREALLOCATED_QTY,SKU_CLASS_ID,SKU_CLASS_DESCR,VENDOR_ID FROM @_@vINV_SKU VI INNER JOIN SYSADM.USER_OWNER UO ON VI.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID @CONDITION@ GROUP BY VI.OWNER_ID,SKU_ID,DESCR,DESCR2,SKU_CLASS_ID,SKU_CLASS_DESCR,VENDOR_ID";
        private string sqlStrBySkuAndLocationID = " SELECT DISTINCT VI.OWNER_ID,SKU_ID,DESCR,DESCR2,LOC,SUM(QTY) AS QTY,SUM(AVAILABLE) AS AVAILABLE,SUM(ALLOCATED_QTY) AS ALLOCATED_QTY, SUM(PICKED_QTY) AS PICKED_QTY,SUM(ONHOLD_QTY) AS ONHOLD_QTY, QTY_LOCATION_MINIMUM,QTY_LOCATION_LIMIT,LOCATION_TYPE,QTY_REPLENISHMENT_OVERRIDE,SKU_CLASS_ID,SKU_CLASS_DESCR,VENDOR_ID FROM @_@vINV_SKU_LOC VI INNER JOIN SYSADM.USER_OWNER UO ON VI.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID @CONDITION@ GROUP BY VI.OWNER_ID,SKU_ID,DESCR,DESCR2,LOC,SKU_CLASS_ID,SKU_CLASS_DESCR,QTY_LOCATION_MINIMUM,QTY_LOCATION_LIMIT,LOCATION_TYPE,QTY_REPLENISHMENT_OVERRIDE,VENDOR_ID";

        public DataSet GetInventoryByQueryEnetiy(InventoryQueryEntity inventoryQueryEntity, List<string> sqlWhere, string whLoginID, PubParasInfo pubParasInfo)
        {
            InventoryQueryEntity inventoryQuery = inventoryQueryEntity;
            string condition = inventoryQuery.Condition;
            string sql = string.Empty;
            switch (condition)
            {
                case "1":
                    sql = this.sqlStrBySku;
                    break;

                case "2":
                    sql = this.sqlStrBySkuAndLocationID;
                    break;

                case "3":
                    sql = this.sqlStrByLpn;
                    break;

                case "4":
                    sql = this.sqlStrByLocationID;
                    break;

                case "5":
                    sql = this.sqlStrByLotID;
                    break;

                case "6":
                    sql = this.sqlStrByLotIDAndLpn;
                    break;

                case "7":
                    sql = this.sqlStrByLotIDAndLoactionID;
                    break;

                case "8":
                    sql = this.sqlStrByLotIDAndLoactionIDAndLpn;
                    break;

                case "9":
                    sql = this.sqlStrByOwnerID;
                    break;
            }
            if (!string.IsNullOrEmpty(sql))
            {
                string sqlcondition = string.Empty;
                for (int i = 0; i < sqlWhere.Count; i++)
                {
                    if (i == 0)
                    {
                        sqlcondition = sqlcondition + " WHERE " + sqlWhere[i].ToString();
                    }
                    else
                    {
                        sqlcondition = sqlcondition + " AND " + sqlWhere[i].ToString();
                    }
                }
                sql = sql.Replace("@CONDITION@", sqlcondition);
                if (!inventoryQuery.IsGetAll)
                {
                    sql = PagingHelper.GetPagingSQL(sql, inventoryQuery.CurrentPage, inventoryQuery.PageSize).Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
                }
                else
                {
                    sql = sql.Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
                }
                return DBHelper.ExecuteDataSet(CommandType.Text, sql.Replace("@_@", whLoginID));
            }
            return null;
        }

        public List<InventoryInfo> GetInventoryInfoByLoc(string loc, string whLoginID)
        {
            string sql = this.SQL_GET_INVENTORY_INFO + " WHERE ILLL.QTY > 0 AND ILLL.LOC = @LOC";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOC", loc) };
            List<InventoryInfo> inventoryInfoList = new List<InventoryInfo>();
            InventoryInfo inventoryInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql.Replace("@_@", whLoginID), paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    inventoryInfo = new InventoryInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["AVAILABLE_QTY"].ToString() != "") ? decimal.Parse(reader["AVAILABLE_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["PICKINPROCESS_QTY"].ToString() != "") ? decimal.Parse(reader["PICKINPROCESS_QTY"].ToString()) : 0M, (reader["PENDING_MOVE_IN"].ToString() != "") ? decimal.Parse(reader["PENDING_MOVE_IN"].ToString()) : 0M, (reader["ARCHIVE_QTY"].ToString() != "") ? decimal.Parse(reader["ARCHIVE_QTY"].ToString()) : 0M, (reader["ARCHIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["ARCHIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["STATUS"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["SKU_DESCR"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                    inventoryInfoList.Add(inventoryInfo);
                }
            }
            return inventoryInfoList;
        }

        public List<InventoryInfo> GetInventoryInfoByLoc(DataBase database, DbTransaction tran, string loc, string whLoginID)
        {
            string sql = this.SQL_GET_INVENTORY_INFO + " WHERE ILLL.QTY > 0 AND ILLL.LOC = @LOC";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOC", loc) };
            List<InventoryInfo> inventoryInfoList = new List<InventoryInfo>();
            InventoryInfo inventoryInfo = null;
            IDataReader reader = database.ExecuteReader(tran, CommandType.Text, sql.Replace("@_@", whLoginID), paras);
            while (reader.Read())
            {
                DateTime CS00002;
                inventoryInfo = new InventoryInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["AVAILABLE_QTY"].ToString() != "") ? decimal.Parse(reader["AVAILABLE_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["PICKINPROCESS_QTY"].ToString() != "") ? decimal.Parse(reader["PICKINPROCESS_QTY"].ToString()) : 0M, (reader["PENDING_MOVE_IN"].ToString() != "") ? decimal.Parse(reader["PENDING_MOVE_IN"].ToString()) : 0M, (reader["ARCHIVE_QTY"].ToString() != "") ? decimal.Parse(reader["ARCHIVE_QTY"].ToString()) : 0M, (reader["ARCHIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["ARCHIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["STATUS"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["SKU_DESCR"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                inventoryInfoList.Add(inventoryInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return inventoryInfoList;
        }

        public List<InventoryInfo> GetInventoryInfoByLotAttribute(LotAttributeInfo lotAttribute, string orderBy, string whLoginID)
        {
            string sql = "SELECT   A.OWNER_ID AS OWNER_ID, A.SKU_ID AS SKU_ID, A.LOT_ATTR01,SUM(A.QTY) AS QTY, SUM(A.ALLOCATED_QTY) AS ALLOCATED_QTY, SUM(A.PICKED_QTY) AS PICKED_QTY, SUM(A.EXPECTED_QTY) AS EXPECTED_QTY, SUM(A.AVAILABLE_QTY) AS AVAILABLE_QTY,SUM(A.PICKINPROCESS_QTY) AS PICKINPROCESS_QTY, SUM(A.PENDING_MOVE_IN) AS PENDING_MOVE_IN,SUM(A.ARCHIVE_QTY) AS ARCHIVE_QTY,  A.STATUS AS STATUS,A.DESCR AS SKU_DESCR  FROM ( SELECT TOP 99999 ILLL.OWNER_ID AS OWNER_ID, ILLL.SKU_ID AS SKU_ID, LA.LOT_ATTR01,ILLL.QTY, ILLL.ALLOCATED_QTY, ILLL.PICKED_QTY, ILLL.EXPECTED_QTY, ILLL.PICKINPROCESS_QTY, ILLL.PENDING_MOVE_IN, ILLL.QTY - ILLL.ALLOCATED_QTY - ILLL.PICKED_QTY - ILLL.EXPECTED_QTY - ILLL.PICKINPROCESS_QTY - ILLL.PENDING_MOVE_IN AS AVAILABLE_QTY,ILLL.ARCHIVE_QTY,  ILLL.STATUS AS STATUS,  SKU.DESCR  FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOT_ATTRIBUTE LA ON ILLL.LOT = LA.LOT LEFT JOIN @_@SKU SKU ON ILLL.OWNER_ID = SKU.OWNER_ID AND ILLL.SKU_ID = SKU.SKU_ID  WHERE ILLL.OWNER_ID IN(@OWNER_ID) AND ILLL.SKU_ID = @SKU_ID AND ILLL.QTY > 0 ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@SKU_ID", lotAttribute.SkuID) };
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr01))
            {
                sql = sql + " AND LA.LOT_ATTR01 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr01);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr02))
            {
                sql = sql + " AND LA.LOT_ATTR02 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr02);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr03))
            {
                sql = sql + " AND LA.LOT_ATTR03 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr03);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr04))
            {
                sql = sql + " AND LA.LOT_ATTR04 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr04);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr05))
            {
                sql = sql + " AND LA.LOT_ATTR05 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr05);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr06))
            {
                sql = sql + " AND LA.LOT_ATTR06 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr06);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr07))
            {
                sql = sql + " AND LA.LOT_ATTR07 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr07);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr08))
            {
                sql = sql + " AND LA.LOT_ATTR08 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr08);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr09))
            {
                sql = sql + " AND LA.LOT_ATTR09 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr09);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr10))
            {
                sql = sql + " AND LA.LOT_ATTR10 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr10);
            }
            DateTime CS00003 = new DateTime();
            if (lotAttribute.ProduceDate != CS00003)
            {
                CS00003 = lotAttribute.ProduceDate;
                sql = sql + " AND LA.PRODUCE_DATE = " + StrUtils.FormatSQLStr(CS00003.ToString());
            }
            CS00003 = new DateTime();
            if (lotAttribute.ExpiryDate != CS00003)
            {
                CS00003 = lotAttribute.ExpiryDate;
                sql = sql + " AND LA.EXPIRY_DATE = " + StrUtils.FormatSQLStr(CS00003.ToString());
            }
            if (!string.IsNullOrEmpty(lotAttribute.ExternalLot))
            {
                sql = sql + " AND LA.EXTERNAL_LOT = " + StrUtils.FormatSQLStr(lotAttribute.ExternalLot);
            }
            if (!string.IsNullOrEmpty(lotAttribute.VendorID))
            {
                sql = sql + " AND LA.VENDOR_ID = " + StrUtils.FormatSQLStr(lotAttribute.VendorID);
            }
            CS00003 = new DateTime();
            if (lotAttribute.ReceiptDate != CS00003)
            {
                CS00003 = lotAttribute.ReceiptDate;
                sql = sql + " AND CAST(CONVERT(VARCHAR(10),LA.RECEIPT_DATE,23) AS DATETIME) = CAST(CONVERT(VARCHAR(10)," + StrUtils.FormatSQLStr(CS00003.ToShortDateString()) + ",23) AS DATETIME)";
            }
            if (!string.IsNullOrEmpty(lotAttribute.Status))
            {
                sql = sql + " AND ILLL.STATUS='" + lotAttribute.Status + "'";
            }
            if (!string.IsNullOrEmpty(orderBy))
            {
                sql = sql + " " + orderBy;
            }
            sql = (sql + " ) A " + " GROUP BY  A.OWNER_ID,A.SKU_ID,A.DESCR,A.STATUS ,A.LOT_ATTR01 ").Replace("@_@", whLoginID);
            List<InventoryInfo> inventoryInfoList = new List<InventoryInfo>();
            InventoryInfo inventoryInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql.Replace("@_@", whLoginID).Replace("@OWNER_ID", lotAttribute.OwnerID), paras))
            {
                while (reader.Read())
                {
                    CS00003 = new DateTime();
                    CS00003 = new DateTime();
                    CS00003 = new DateTime();
                    CS00003 = new DateTime();
                    CS00003 = new DateTime();
                    CS00003 = new DateTime();
                    inventoryInfo = new InventoryInfo(0, "", "", "", "", reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["AVAILABLE_QTY"].ToString() != "") ? decimal.Parse(reader["AVAILABLE_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["PICKINPROCESS_QTY"].ToString() != "") ? decimal.Parse(reader["PICKINPROCESS_QTY"].ToString()) : 0M, (reader["PENDING_MOVE_IN"].ToString() != "") ? decimal.Parse(reader["PENDING_MOVE_IN"].ToString()) : 0M, (reader["ARCHIVE_QTY"].ToString() != "") ? decimal.Parse(reader["ARCHIVE_QTY"].ToString()) : 0M, CS00003, reader["STATUS"].ToString(), null, "", CS00003, "", CS00003, reader["SKU_DESCR"].ToString(), reader["LOT_ATTR01"].ToString(), "", "", "", "", "", "", "", "", "", CS00003, CS00003, CS00003, "", "");
                    inventoryInfoList.Add(inventoryInfo);
                }
            }
            return inventoryInfoList;
        }

        public List<InventoryInfo> GetInventoryInfoByLotAttribute(LotAttributeInfo lotAttribute, string loc, string lpnID, string orderBy, string whLoginID)
        {
            string sql = this.SQL_GET_INVENTORY_INFO + " WHERE ILLL.OWNER_ID = @OWNER_ID AND ILLL.SKU_ID = @SKU_ID AND ILLL.QTY > 0 ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", lotAttribute.OwnerID), new SqlParameter("@SKU_ID", lotAttribute.SkuID) };
            if (!string.IsNullOrEmpty(loc))
            {
                sql = sql + " AND ILLL.LOC = " + StrUtils.FormatSQLStr(loc);
            }
            if (!string.IsNullOrEmpty(lpnID))
            {
                sql = sql + " AND ILLL.LPN_ID = " + StrUtils.FormatSQLStr(lpnID);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr01))
            {
                sql = sql + " AND LA.LOT_ATTR01 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr01);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr02))
            {
                sql = sql + " AND LA.LOT_ATTR02 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr02);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr03))
            {
                sql = sql + " AND LA.LOT_ATTR03 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr03);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr04))
            {
                sql = sql + " AND LA.LOT_ATTR04 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr04);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr05))
            {
                sql = sql + " AND LA.LOT_ATTR05 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr05);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr06))
            {
                sql = sql + " AND LA.LOT_ATTR06 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr06);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr07))
            {
                sql = sql + " AND LA.LOT_ATTR07 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr07);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr08))
            {
                sql = sql + " AND LA.LOT_ATTR08 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr08);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr09))
            {
                sql = sql + " AND LA.LOT_ATTR09 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr09);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr10))
            {
                sql = sql + " AND LA.LOT_ATTR10 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr10);
            }
            DateTime CS00003 = new DateTime();
            if (lotAttribute.ProduceDate != CS00003)
            {
                CS00003 = lotAttribute.ProduceDate;
                sql = sql + " AND LA.PRODUCE_DATE = " + StrUtils.FormatSQLStr(CS00003.ToString());
            }
            CS00003 = new DateTime();
            if (lotAttribute.ExpiryDate != CS00003)
            {
                CS00003 = lotAttribute.ExpiryDate;
                sql = sql + " AND LA.EXPIRY_DATE = " + StrUtils.FormatSQLStr(CS00003.ToString());
            }
            if (!string.IsNullOrEmpty(lotAttribute.ExternalLot))
            {
                sql = sql + " AND LA.EXTERNAL_LOT = " + StrUtils.FormatSQLStr(lotAttribute.ExternalLot);
            }
            if (!string.IsNullOrEmpty(lotAttribute.VendorID))
            {
                sql = sql + " AND LA.VENDOR_ID = " + StrUtils.FormatSQLStr(lotAttribute.VendorID);
            }
            CS00003 = new DateTime();
            if (lotAttribute.ReceiptDate != CS00003)
            {
                CS00003 = lotAttribute.ReceiptDate;
                sql = sql + " AND CAST(CONVERT(VARCHAR(10),LA.RECEIPT_DATE,23) AS DATETIME) = CAST(CONVERT(VARCHAR(10)," + StrUtils.FormatSQLStr(CS00003.ToShortDateString()) + ",23) AS DATETIME)";
            }
            if (!string.IsNullOrEmpty(lotAttribute.Status))
            {
                sql = sql + " AND ILLL.STATUS='" + lotAttribute.Status + "'";
            }
            if (!string.IsNullOrEmpty(orderBy))
            {
                sql = sql + " " + orderBy;
            }
            sql = sql.Replace("@_@", whLoginID);
            List<InventoryInfo> inventoryInfoList = new List<InventoryInfo>();
            InventoryInfo inventoryInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql.Replace("@_@", whLoginID), paras))
            {
                while (reader.Read())
                {
                    inventoryInfo = new InventoryInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["AVAILABLE_QTY"].ToString() != "") ? decimal.Parse(reader["AVAILABLE_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["PICKINPROCESS_QTY"].ToString() != "") ? decimal.Parse(reader["PICKINPROCESS_QTY"].ToString()) : 0M, (reader["PENDING_MOVE_IN"].ToString() != "") ? decimal.Parse(reader["PENDING_MOVE_IN"].ToString()) : 0M, (reader["ARCHIVE_QTY"].ToString() != "") ? decimal.Parse(reader["ARCHIVE_QTY"].ToString()) : 0M, (reader["ARCHIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["ARCHIVE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["STATUS"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["SKU_DESCR"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                    inventoryInfoList.Add(inventoryInfo);
                }
            }
            return inventoryInfoList;
        }

        public List<InventoryInfo> GetInventoryInfoByLotAttribute(DataBase database, DbTransaction tran, LotAttributeInfo lotAttribute, string loc, string lpnID, string orderBy, string whLoginID)
        {
            string sql = this.SQL_GET_INVENTORY_INFO + " WHERE ILLL.OWNER_ID = @OWNER_ID AND ILLL.SKU_ID = @SKU_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", lotAttribute.OwnerID), new SqlParameter("@SKU_ID", lotAttribute.SkuID) };
            if (!string.IsNullOrEmpty(loc))
            {
                sql = sql + " AND ILLL.LOC = " + StrUtils.FormatSQLStr(loc);
            }
            if (!string.IsNullOrEmpty(lpnID))
            {
                sql = sql + " AND ILLL.LPN_ID = " + StrUtils.FormatSQLStr(lpnID);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr01))
            {
                sql = sql + " AND LA.LOT_ATTR01 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr01);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr02))
            {
                sql = sql + " AND LA.LOT_ATTR02 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr02);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr03))
            {
                sql = sql + " AND LA.LOT_ATTR03 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr03);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr04))
            {
                sql = sql + " AND LA.LOT_ATTR04 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr04);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr05))
            {
                sql = sql + " AND LA.LOT_ATTR05 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr05);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr06))
            {
                sql = sql + " AND LA.LOT_ATTR06 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr06);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr07))
            {
                sql = sql + " AND LA.LOT_ATTR07 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr07);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr08))
            {
                sql = sql + " AND LA.LOT_ATTR08 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr08);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr09))
            {
                sql = sql + " AND LA.LOT_ATTR09 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr09);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr10))
            {
                sql = sql + " AND LA.LOT_ATTR10 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr10);
            }
            DateTime CS00003 = new DateTime();
            if (lotAttribute.ProduceDate != CS00003)
            {
                CS00003 = lotAttribute.ProduceDate;
                sql = sql + " AND LA.PRODUCE_DATE = " + StrUtils.FormatSQLStr(CS00003.ToString());
            }
            CS00003 = new DateTime();
            if (lotAttribute.ExpiryDate != CS00003)
            {
                CS00003 = lotAttribute.ExpiryDate;
                sql = sql + " AND LA.EXPIRY_DATE = " + StrUtils.FormatSQLStr(CS00003.ToString());
            }
            if (!string.IsNullOrEmpty(lotAttribute.ExternalLot))
            {
                sql = sql + " AND LA.EXTERNAL_LOT = " + StrUtils.FormatSQLStr(lotAttribute.ExternalLot);
            }
            if (!string.IsNullOrEmpty(lotAttribute.VendorID))
            {
                sql = sql + " AND LA.VENDOR_ID = " + StrUtils.FormatSQLStr(lotAttribute.VendorID);
            }
            CS00003 = new DateTime();
            if (lotAttribute.ReceiptDate != CS00003)
            {
                CS00003 = lotAttribute.ReceiptDate;
                sql = sql + " AND CAST(CONVERT(VARCHAR(10),LA.RECEIPT_DATE,23) AS DATETIME) = CAST(CONVERT(VARCHAR(10)," + StrUtils.FormatSQLStr(CS00003.ToShortDateString()) + ",23) AS DATETIME)";
            }
            if (!string.IsNullOrEmpty(lotAttribute.Status))
            {
                sql = sql + " AND ILLL.STATUS='" + lotAttribute.Status + "'";
            }
            if (!string.IsNullOrEmpty(orderBy))
            {
                sql = sql + " " + orderBy;
            }
            sql = sql.Replace("@_@", whLoginID);
            List<InventoryInfo> inventoryInfoList = new List<InventoryInfo>();
            InventoryInfo inventoryInfo = null;
            using (IDataReader reader = database.ExecuteReader(tran, CommandType.Text, sql.Replace("@_@", whLoginID), paras))
            {
                while (reader.Read())
                {
                    inventoryInfo = new InventoryInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["AVAILABLE_QTY"].ToString() != "") ? decimal.Parse(reader["AVAILABLE_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["PICKINPROCESS_QTY"].ToString() != "") ? decimal.Parse(reader["PICKINPROCESS_QTY"].ToString()) : 0M, (reader["PENDING_MOVE_IN"].ToString() != "") ? decimal.Parse(reader["PENDING_MOVE_IN"].ToString()) : 0M, (reader["ARCHIVE_QTY"].ToString() != "") ? decimal.Parse(reader["ARCHIVE_QTY"].ToString()) : 0M, (reader["ARCHIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["ARCHIVE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["STATUS"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["SKU_DESCR"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                    inventoryInfoList.Add(inventoryInfo);
                }
            }
            return inventoryInfoList;
        }

        public InventoryInfo GetInventoryInfoByLpnID(string lpnID, string whLoginID)
        {
            string sql = this.SQL_GET_INVENTORY_INFO + " WHERE ILLL.QTY > 0 AND LPN_ID = @LPN_ID";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LPN_ID", lpnID) };
            InventoryInfo inventoryInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql.Replace("@_@", whLoginID), paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    inventoryInfo = new InventoryInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["AVAILABLE_QTY"].ToString() != "") ? decimal.Parse(reader["AVAILABLE_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["PICKINPROCESS_QTY"].ToString() != "") ? decimal.Parse(reader["PICKINPROCESS_QTY"].ToString()) : 0M, (reader["PENDING_MOVE_IN"].ToString() != "") ? decimal.Parse(reader["PENDING_MOVE_IN"].ToString()) : 0M, (reader["ARCHIVE_QTY"].ToString() != "") ? decimal.Parse(reader["ARCHIVE_QTY"].ToString()) : 0M, (reader["ARCHIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["ARCHIVE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["STATUS"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["SKU_DESCR"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : new DateTime(), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                }
            }
            return inventoryInfo;
        }

        public InventoryInfo GetInventoryInfoByLpnID(DataBase database, DbTransaction tran, string lpnID, string whLoginID)
        {
            string sql = this.SQL_GET_INVENTORY_INFO + " WHERE ILLL.QTY > 0 AND LPN_ID = @LPN_ID";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LPN_ID", lpnID) };
            InventoryInfo inventoryInfo = null;
            IDataReader reader = database.ExecuteReader(tran, CommandType.Text, sql.Replace("@_@", whLoginID), paras);
            if (reader.Read())
            {
                DateTime CS00003;
                inventoryInfo = new InventoryInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["AVAILABLE_QTY"].ToString() != "") ? decimal.Parse(reader["AVAILABLE_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["PICKINPROCESS_QTY"].ToString() != "") ? decimal.Parse(reader["PICKINPROCESS_QTY"].ToString()) : 0M, (reader["PENDING_MOVE_IN"].ToString() != "") ? decimal.Parse(reader["PENDING_MOVE_IN"].ToString()) : 0M, (reader["ARCHIVE_QTY"].ToString() != "") ? decimal.Parse(reader["ARCHIVE_QTY"].ToString()) : 0M, (reader["ARCHIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["ARCHIVE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["STATUS"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["SKU_DESCR"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : new DateTime(), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return inventoryInfo;
        }

        public List<InventoryInfo> GetInventoryInfoByReplenishment(DataBase database, DbTransaction tran, string OwnerID, string skuID, List<string> locUsage, string invStatus, string orderBy, string whLoginID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT   ILLL.OWNER_ID AS OWNER_ID, ILLL.SKU_ID AS SKU_ID,ILLL.QTY AS QTY,ILLL.LOC,ILLL.LOT,ILLL.LPN_ID, ILLL.ALLOCATED_QTY AS ALLOCATED_QTY, ILLL.PICKED_QTY AS PICKED_QTY, ILLL.EXPECTED_QTY AS EXPECTED_QTY, ILLL.PICKINPROCESS_QTY AS PICKINPROCESS_QTY, ILLL.PENDING_MOVE_IN AS PENDING_MOVE_IN, (ILLL.QTY - ILLL.ALLOCATED_QTY - ILLL.PICKED_QTY - ILLL.EXPECTED_QTY - ILLL.PICKINPROCESS_QTY - ILLL.PENDING_MOVE_IN) AS AVAILABLE_QTY, ILLL.ARCHIVE_QTY AS ARCHIVE_QTY,  ILLL.STATUS AS STATUS,ILLL.LOT AS LOT ");
            strSql.Append(" FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOT_ATTRIBUTE LA ON ILLL.LOT = LA.LOT ");
            string strLocUsage = string.Empty;
            if (locUsage.Count > 0)
            {
                strLocUsage = " AND  (";
                for (int i = 0; locUsage.Count > i; i++)
                {
                    if (i > 0)
                    {
                        strLocUsage = strLocUsage + " OR ";
                    }
                    strLocUsage = strLocUsage + "  L.LOC_USAGE='" + locUsage[i] + "'";
                }
                strLocUsage = strLocUsage + " )";
            }
            strSql.AppendFormat(" WHERE ILLL.OWNER_ID =@OWNER_ID AND ILLL.SKU_ID = @SKU_ID AND ILLL.QTY > 0 AND ILLL.LOC =(SELECT L.LOC FROM @_@LOCATION L WHERE   ILLL.LOC=L.LOC  {0} )  AND ILLL.STATUS='{1}' AND (ILLL.QTY - ILLL.ALLOCATED_QTY - ILLL.PICKED_QTY - ILLL.EXPECTED_QTY - ILLL.PICKINPROCESS_QTY - ILLL.PENDING_MOVE_IN- ILLL.EXPECTED_QTY) >0  ", strLocUsage, invStatus);
            if (!string.IsNullOrEmpty(orderBy))
            {
                strSql.AppendFormat(" {0}", orderBy);
            }
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", OwnerID), new SqlParameter("@SKU_ID", skuID) };
            List<InventoryInfo> inventoryInfoList = new List<InventoryInfo>();
            InventoryInfo inventoryInfo = null;
            using (IDataReader reader = database.ExecuteReader(tran, CommandType.Text, strSql.ToString().Replace("@_@", whLoginID), paras))
            {
                while (reader.Read())
                {
                    DateTime CS00003 = new DateTime();
                    CS00003 = new DateTime();
                    CS00003 = new DateTime();
                    CS00003 = new DateTime();
                    CS00003 = new DateTime();
                    CS00003 = new DateTime();
                    inventoryInfo = new InventoryInfo(0, "", reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["AVAILABLE_QTY"].ToString() != "") ? decimal.Parse(reader["AVAILABLE_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["PICKINPROCESS_QTY"].ToString() != "") ? decimal.Parse(reader["PICKINPROCESS_QTY"].ToString()) : 0M, (reader["PENDING_MOVE_IN"].ToString() != "") ? decimal.Parse(reader["PENDING_MOVE_IN"].ToString()) : 0M, (reader["ARCHIVE_QTY"].ToString() != "") ? decimal.Parse(reader["ARCHIVE_QTY"].ToString()) : 0M, CS00003, reader["STATUS"].ToString(), null, "", CS00003, "", CS00003, "", "", "", "", "", "", "", "", "", "", "", CS00003, CS00003, CS00003, "", "");
                    inventoryInfoList.Add(inventoryInfo);
                }
            }
            return inventoryInfoList;
        }

        public List<InventoryInfo> GetInventoryInfoByWhere(string where, string whLoginID)
        {
            string sql = this.SQL_GET_INVENTORY_INFO + " WHERE ILLL.QTY > 0 " + where;
            List<InventoryInfo> inventoryInfoList = new List<InventoryInfo>();
            InventoryInfo inventoryInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql.Replace("@_@", whLoginID)))
            {
                while (reader.Read())
                {
                    DateTime CS00001;
                    inventoryInfo = new InventoryInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["AVAILABLE_QTY"].ToString() != "") ? decimal.Parse(reader["AVAILABLE_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["PICKINPROCESS_QTY"].ToString() != "") ? decimal.Parse(reader["PICKINPROCESS_QTY"].ToString()) : 0M, (reader["PENDING_MOVE_IN"].ToString() != "") ? decimal.Parse(reader["PENDING_MOVE_IN"].ToString()) : 0M, (reader["ARCHIVE_QTY"].ToString() != "") ? decimal.Parse(reader["ARCHIVE_QTY"].ToString()) : 0M, (reader["ARCHIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["ARCHIVE_DATE"].ToString()) : (CS00001 = new DateTime()), reader["STATUS"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00001 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00001 = new DateTime()), reader["SKU_DESCR"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00001 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00001 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00001 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                    inventoryInfoList.Add(inventoryInfo);
                }
            }
            return inventoryInfoList;
        }

        private SqlParameter[] Set_InventoryMgt_Parameters(InventoryQueryEntity inventoryQuery)
        {
            SqlParameter[] paramArray = new SqlParameter[] { new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SKU, SqlDbType.VarChar, 50), new SqlParameter(this.LOTATTRIBUTE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.LOCATION_ID, SqlDbType.VarChar, 20), new SqlParameter(this.LPN, SqlDbType.VarChar, 20), new SqlParameter(this.QTY, SqlDbType.Decimal) };
            if (!string.IsNullOrEmpty(inventoryQuery.OwnerID))
            {
                paramArray[0].Value = inventoryQuery.OwnerID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = inventoryQuery.Sku;
            paramArray[2].Value = inventoryQuery.LotAttributeID;
            paramArray[3].Value = inventoryQuery.Location;
            paramArray[4].Value = inventoryQuery.LPN;
            if (!string.IsNullOrEmpty(inventoryQuery.QTY))
            {
                paramArray[5].Value = inventoryQuery.QTY;
                return paramArray;
            }
            paramArray[5].Value = 0.0M;
            return paramArray;
        }
    }
}

